import os
import re
import pymysql
import functools
from flask import Flask,render_template,redirect,request,session
from openpyxl import load_workbook
from .sqlhelper2 import db
def create_app():
    app = Flask(__name__)
    app.secret_key = 'asdaxxxjsd'
    def auth(func):
        @functools.wraps(func)
        def inner(*args,**kwargs):
            username = session.get('xxx')
            if not username:
                return redirect('/login')
            return func(*args,**kwargs)
        return inner
    @app.route('/login',methods=['GET','POST'])
    def login():
        if request.method == 'GET':
            return render_template("login.html")
        user = request.form.get('user')
        pwd  = request.form.get('pwd')
        if user == 'root45678' and pwd == 'root45678':
            session['xxx'] = 'changxin'
            return redirect('/index')
        else:
            error = '用户名或密码错误'
            return render_template('login.html',error=error)
    @app.route('/index')
    @auth
    def index():
        results = db.fetchall('select * from book')
        return render_template('index.html',data_dict=results)
    @app.route('/add',methods=['GET','POST'])
    @auth
    def add():
        if request.method == 'GET':
            return render_template('add.html')
        title = request.form.get('title')
        author  = request.form.get('author')
        price = request.form.get('price')
        if title and author and price:
            sql_add = 'insert into book (title,author,price) values(%s,%s,%s)'
            values = (title,author,price)
            pattern = r'^[1-9]+\d*(\.\d+)?$|^[0](\.\d+)?$'
            if bool(re.match(pattern,price)):
                db.exec(sql_add,values)
                return redirect('/index')
            else:
                error = '价格的格式不正确，请输入正确的数字'
                return render_template('add.html',error=error) 
        else:
            error = '添加失败，不能有单元格为空'
            return render_template('add.html',error=error)
    @app.route('/edit',methods=['GET','POST'])
    @auth
    def edit():
        if request.method == 'GET':
            title = request.args.get('title')
            author = request.args.get('author')
            price = request.args.get('price')
            edit_dict = {'title':title,'author':author,'price':price}
            return render_template('edit.html',edit_dict = edit_dict)
        title = request.form.get('title')
        author  = request.form.get('author')
        price = request.form.get('price')
        edit_dict = {'title':title,'author':author,'price':price}
        sql_edit = "UPDATE book SET price = %s WHERE title = %s and author = %s"
        values = (price,title,author)
        pattern = r'^[1-9]+\d*(\.\d+)?$|^[0](\.\d+)?$'
        if price:
            if bool(re.match(pattern,price)):
                db.exec(sql_edit,values)
                return redirect('/index')
            else:
                error = '价格的格式不正确，请输入正确的数字'
                return render_template('edit.html',error=error,edit_dict=edit_dict) 
        else:
            error = '修改失败，价格不能为空'
            return render_template('edit.html',error=error,edit_dict=edit_dict)
    @app.route('/del')
    @auth
    def dell():
        title = request.args.get('title')
        author  = request.args.get('author')
        price = request.args.get('price')
        sql_del = "DELETE FROM book  where price = %s and title = %s and author = %s"
        values = (price,title,author)
        db.exec(sql_del,values)
        return redirect('/index')
    @app.route('/upload',methods=['GET','POST'])
    @auth
    def upload_file():
        file = request.files['fileUpload']
        filename = file.filename
        file_path = os.path.join('/data/book_management_system/pro_excel/pro_excel/uploads', filename) # linux 路径
        # file_path = os.path.join('D:\\desktop\\为何如此怠惰兄弟\\flask\\day90\\pro_excel\\pro_excel\\uploads', filename) # windows路径
        file.save(file_path)
        workbook = load_workbook(file_path)
        worksheet = workbook['Sheet1']
        for row in worksheet.iter_rows(values_only=True):
            sql_add = 'insert into book (title,author,price) values(%s,%s,%s)'
            db.exec(sql_add,row)
        os.remove(file_path)
        return redirect('/index')
    @app.route('/')
    @auth
    def jump():
        return redirect('/index')
        # cursor = connection.cursor()
        # sql_add = "INSERT INTO yourtable (column1, column2, column3) VALUES (%s, %s, %s)"
    # from .views.wy import xwy
    # from .views.my import xmy
    # app.register_blueprint(xmy,url_prefix='/web') # 这里也需要注册
    # app.register_blueprint(xwy,url_prefix='/admin') # 这里也需要注册 与django的区别是django在settings种注册
    # # 我不能自己干 我要招两个小弟 两个小弟就是两个蓝图 我把两个蓝图放在views里面
    # # 我可以将单独的功能放在各自的蓝图里面 由我负责触发
    # # 所以这个请求就一共有5个 url 我一个 他两个 他两个
    return app

